using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Data.Common;
using System.Data;

namespace RikMigrations
{
	public abstract class DbProvider
	{
		#region Basic Commands
		static string defaultConnectionString;
		public static string DefaultConnectionString
		{
			get { return defaultConnectionString ?? System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString; }
			set { defaultConnectionString = value; }
		}

		string connectionString;
		public string ConnectionString
		{
			get { return connectionString ?? DefaultConnectionString; }
			set { connectionString = value; }
		}

		DbConnection transactedConnection;
		DbTransaction transaction;
		int transactionCount; // NOTE: This is really just for testing, shouldn't be used in production

		#region Properties
		private int? commandTimeout;
		public int? CommandTimeout
		{
			get { return commandTimeout; }
			set { commandTimeout = value; }
		}
		#endregion

		public void CreateTransaction()
		{
			transactedConnection = CreateConnection();
			if ((transaction == null) || (transaction.Connection == null))
			{
				transaction = transactedConnection.BeginTransaction();
				transactionCount = 1;
			}
			else
				transactionCount++;
		}

		public void CommitTransaction()
		{
			if (--transactionCount == 0)
			{
				transaction.Commit();
				transaction = null;
				transactedConnection.Dispose();
				transactedConnection = null;
			}
		}

		public void RollbackTransaction()
		{
			if (transaction != null)
			{
				transaction.Rollback();
				transaction = null;
				transactedConnection.Dispose();
				transactedConnection = null;
			}
			if (--transactionCount > 0)
				throw new Exception("Inner transaction failed");
		}

		public DbConnection CreateConnection()
		{
			if (transactedConnection != null)
				return transactedConnection;
			return CreateNewConnection();
		}

		protected abstract DbConnection CreateNewConnection();
		protected abstract DbParameter CreateParameter(int i, object value);

		public void ExecuteNonQuery(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			try
			{
				ExecuteNonQuery(conn, sql, parameters);
			}
			finally
			{
				if (transactedConnection == null)
					conn.Dispose();
			}
		}

		public void ExecuteNonQuery(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				cmd.ExecuteNonQuery();
			}
		}

		public object ExecuteScalar(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			try
			{
				return ExecuteScalar(conn, sql, parameters);
			}
			finally
			{
				if (transactedConnection == null)
					conn.Dispose();
			}
		}

		public object ExecuteScalar(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteScalar();
			}
		}

		public DbDataReader ExecuteReader(string sql, params object[] parameters)
		{
			DbConnection conn = CreateConnection();
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteReader(transactedConnection == null
					 ? CommandBehavior.CloseConnection : CommandBehavior.Default);
			}
		}

		public DbDataReader ExecuteReader(DbConnection conn, string sql, params object[] parameters)
		{
			using (DbCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				cmd.Connection = conn;
				if (CommandTimeout != null)
					cmd.CommandTimeout = CommandTimeout.Value;
				if (transactedConnection != null)
					cmd.Transaction = transaction;

				for (int i = 0; i < parameters.Length; i++)
				{
					if (parameters[i] == null)
						parameters[i] = DBNull.Value;
					cmd.Parameters.Add(CreateParameter(i, parameters[i]));
				}

				return cmd.ExecuteReader();
			}
		}
		#endregion

		#region Introspection
		public abstract bool TableExists(string name);
        public abstract bool ViewExists(string name);
		public abstract bool ColumnExists(string table, string name);
	    public abstract bool StoredProcedureExists(string name);
		public abstract List<ForeignKey> FindForeignKeys(Table table);
		#endregion

		#region Migration Utilities
		public virtual Table AddTable(string name)
		{
			return new Table(this, name, true);
		}

		public virtual Table AlterTable(string name)
		{
			return new Table(this, name, false);
		}

        public virtual View AddView(string name, string script)
        {
            return new View(this, name, true, script);
        }

		public abstract void DropTable(string name);
	    public abstract void DropView(string name);
		#endregion

		public abstract int GetModuleVersion(string moduleName);
		public abstract void SetModuleVersion(string moduleName, int version);

		#region Commands

		public abstract Command GetCreateTableCommand(Table table);
		public abstract Command GetAddColumnCommand(Column column);
		public abstract Command GetDropColumnCommand(Table table, string columnName);
		public abstract Command GetAddForeignKeyCommand(ForeignKey key);
		public abstract Command GetRenameTableCommand(Column column, string newName);
	    public abstract Command GetCreateViewCommand(View view);
	    public abstract Command GetCreateSPCommand(StoredProcedure sp);
        public abstract Command GetInsertCommand(string tableName, object objectToInsert, bool IsIdentityInsert);

		#endregion

		#region Utilities
		public virtual string GetLiteral(Type type, object value)
		{
			if (value.GetType() == typeof(string) && type != typeof(string))
				return (string)value;

			if (type == typeof(bool))
				return ((bool)value) ? "1" : "0";
			else if (type == typeof(Guid) ||
						type == typeof(string))
				return "'" + value.ToString() + "'";
			return value.ToString();
		}
		#endregion

        public void InsertDataInto(string tableName, object objectToInsert, bool isIdentityInsert)
	    {
            GetInsertCommand(tableName, objectToInsert, isIdentityInsert).Execute(this);
	    }
	}
}